SQL RANK() Function Explained By Practical Examples

2024-07-12 17:17| 来源: 网络整理| 查看: 265

Summary: in this tutorial, you will learn how to use SQL RANK() function to find the rank of each row in the result set.

Introduction to MySQL RANK() function

The RANK() function is a window function that assigns a rank to each row in the partition of a result set.

The rank of a row is determined by one plus the number of ranks that come before it.

The syntax of the RANK() function is as follows

RANK() OVER ( PARTITION BY [{,...}] ORDER BY [ASC|DESC], [{,...}] ) Code language: SQL (Structured Query Language) (sql)

In this syntax:

First, the PARTITION BY clause distributes the rows in the result set into partitions by one or more criteria.Second, the ORDER BY clause sorts the rows in each a partition.The RANK() function is operated on the rows of each partition and re-initialized when crossing each partition boundary.

The same column values receive the same ranks. When multiple rows share the same rank, the rank of the next row is not consecutive. This is similar to Olympic medaling in that if two athletes share the gold medal, there is no silver medal.

The following statements create a new table name t and insert some sample data:

CREATE TABLE t ( col CHAR ); INSERT INTO t(col) VALUES('A'),('B'),('B'),('C'),('D'),('D'),('E'); SELECT * FROM t; Code language: SQL (Structured Query Language) (sql)

The following statement uses the RANK() function to assign ranks to the rows of the result set:

SELECT col, RANK() OVER ( ORDER BY col ) myrank FROM t; Code language: SQL (Structured Query Language) (sql)

The following picture shows the output:

As clearly shown in the output, the second and third rows share the same rank because they have the same value. The fourth row gets the rank 4 because the RANK() function skips the rank 3.

Note that if you want to have consecutive ranks, you can use the DENSE_RANK() function.

SQL RANK() function examples

We will use the employees and departments table from the sample database for the demonstration.

Using SQL RANK() function over the result set example

The following statement ranks employees by their salaries:

SELECT first_name, last_name, salary, RANK() OVER (ORDER BY salary) salary_rank FROM employees; Code language: SQL (Structured Query Language) (sql)

The following shows the partial output:

SQL Rank Function over result set example

In this example, we omitted the PARTITION BY clause so the whole result set was treated as a single partition.

The ORDER BY clause sorted the rows in the result by salary. The RANK() function then is applied to each row in the result considering the order of employees by salary in descending order.

Using SQL RANK() function over partition example

The following statement finds the employees who have the second highest salary in their departments:

WITH payroll AS ( SELECT first_name, last_name, department_id, salary, RANK() OVER ( PARTITION BY department_id ORDER BY salary) salary_rank FROM employees ) SELECT first_name, last_name, department_name, salary FROM payroll p INNER JOIN departments d ON d.department_id = p.department_id WHERE salary_rank = 2; Code language: SQL (Structured Query Language) (sql)

In the common table expression, we find the salary ranks of employees by their departments:

First, the PARTITION BY clause divided the employee records by their departments into partitions.Then, the ORDER BY clause sorted employees in each partition by salary.Finally, the RANK() function assigned ranks to employees per partition. The employees who have the same salary got the same rank.

The following picture illustrates the partial result set of the common table expression:

The outer query joined selected only employees whose salary rank is 2. It also joined with the departments table to return the department names in the final result set.

The following picture shows the output of the query:

SQL Rank Function over partition example

In this tutorial, you have learned how to use the SQL RANK() function that assigns a rank to each row in a result set.

Was this tutorial helpful ?

【本文地址】

公司简介

联系我们